ALTER   function fn_get_shipping_id_by_qty ( @qty_in int, @date_in datetime, @lot_id int) 
returns  int
as

begin
	declare @temp_shipping_id int 
	declare @temp_shipping_qty int 
	declare @temp_ship_date datetime 
	declare @shipping_cursor cursor
	declare @return_shipping_id int
	declare @diff_qty int
	declare @temp_diff_qty int
	
	set @shipping_cursor = cursor for select shippedLot_id, Ship_Qty, Ship_Date from Shipped_Lot where lot_id = @lot_id and status = 'Transit' and Ship_Date <= @date_in and Ship_Qty <= @qty_in order by Ship_Date asc
	open @shipping_cursor
	fetch next from @shipping_cursor into @temp_shipping_id, @temp_shipping_qty, @temp_ship_date
	
	set @diff_qty = -1
	while @@fetch_status = 0
	begin
		set @temp_diff_qty = @temp_shipping_qty - @qty_in
		if @diff_qty = -1
		begin
			set @diff_qty = @temp_diff_qty
			set @return_shipping_id = @temp_shipping_id
		end 
		else
		begin
			if @temp_diff_qty < @diff_qty
			begin
				set @diff_qty = @temp_diff_qty	
				set @return_shipping_id = @temp_shipping_id
			end
		end
		fetch next from @shipping_cursor into @temp_shipping_id, @temp_shipping_qty, @temp_ship_date
	end
	close @shipping_cursor
	deallocate @shipping_cursor

	return @return_shipping_id
end